The Excel worksheet form that appears below is to be used to recreate Example E and Exhibit 13–8. Download the workbook containing this form from Connect, where you will also receive instructions about how to use this worksheet form.
Exhibit 13 – 8: The Net Present Value Method—An Extended Example
You should proceed to the requirements below only after completing your worksheet. Note that you may get a slightly different net present value from that shown in the text due to the precision of the calculations.
Required:
1. Check your worksheet by changing the discount rate to 10%. The net present value should now be between $56,495 and $56,518—depending on the precision of the calculations. If you do not get an answer in this range, find the errors in your worksheet and correct them. Explain why the net present value has increased as a result of reducing the discount rate from 14% to 10%.
2. The company is considering another project involving the purchase of new equipment. Change the data area of your worksheet to match the following:
Data
Example E
Cost of equipment needed . . . . . . . . . . . . . . . $120,000
Working capital needed . . . . . . . . . . . . . . . . . . $80,000
Overhaul of equipment in four years . . . . . . . . $40,000
Salvage value of the equipment in five years . . $20,000
Annual revenues and costs:
Sales revenues . . . . . . . . . . . . . . . . . . . . . . . . $255,000
Cost of goods sold . . . . . . . . . . . . . . . . . . . . . $160,000
Out-of-pocket operating costs . . . . . . . . . . . . . $50,000
Discount rate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14%
a. What is the net present value of the project?
b. Experiment with changing the discount rate in one percent increments (e.g., 13%,
12%, 15%, etc.). At what interest rate does the net present value turn from negative to positive?
c. The internal rate of return is between what two whole discount rates (e.g., between 10% and 11%, between 11% and 12%, between 12% and 13%, between 13% and 14%, etc.)?
d. Reset the discount rate to 14%. Suppose the salvage value is uncertain. How large would the salvage value have to be to result in a positive net present value?
SOLUTION
The completed worksheet is shown below.
Note: Your worksheet may differ from the above in rows 29 and 30. The worksheet above has been set to use the rounded-off discount factors rather than more exact factors without rounding. For example, the factor 0.519 is rounded off from 0.519368664. If the more exact factor is used to calculate the present value of the $150,000 total cash flow at the end of year 5, the answer is $77,905 rather than $77,850. These rounding errors cumulate so that the more exact net present value is $31,493 rather than the $31,410 as displayed. Either answer is okay.
The completed worksheet, with formulas displayed, is shown below.
1. With the change in the discount rate, the result is:
The net present value increases because the positive cash inflows occur in the future. When the discount rate decreases, the future cash flows have a larger present value.
2. For the new project, the worksheet should look like this:
a. The net present value of the project is $(17,340). Again, your answer may differ due to the precision of the calculations.
b. Increasing the discount rate results in making the negative net present value even more negative. Decreasing the discount rate improves the net present value. It turns positive when decreasing the discount rate from 11% to 10% as shown below.
c. The internal rate of return is the discount rate at which the net present value is zero. This occurs somewhere between the discount rates 10% and 11%. The net present value at 10% is $5,330 as shown above. The net present value at 11% is $(740) as shown below. Therefore, the internal rate of return is between 10% and 11%.
Continue to the next page…
d. The amount of future uncertain salvage value that would be required to make the net present value positive, which is $53,410 ($33,410 + $20,000), can be found by experimenting with the salvage value in the worksheet. It can also be computed using the formula from the text as follows: